set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;

with interceptor_area as ( --区域异常
    select
         province
        ,province_id
        ,city
        ,city_id
        ,area
        ,area_id
        ,if(effect_time_start is null, '1999-01-01 01:01:01', effect_time_start) as effect_time_start
        ,if(effect_time_end   is null, '9999-01-01 01:01:01', effect_time_end  ) as effect_time_end
    from jms_dim.dim_yl_oms_order_interceptor_base_dt
    where dt = date_sub('{{ execution_date | cst_ds }}',1)
      and data_type = 'area'
      and is_delete = 0           --未删除
      and is_enable = 1           --已启用 0311001
      and ((effect_time_start is not null and effect_time_end is not null)
        or (effect_time_start is null and effect_time_end is null and effect_time_type = 0))--生效时间类型 0 无限 ，1 有效时间断
    group by province
            ,province_id
            ,city
            ,city_id
            ,area
            ,area_id
            ,effect_time_start
            ,effect_time_end
),
area_massage_all as (
   select
        code
       ,name
       ,effect_time_start
       ,effect_time_end
   from (
       select *
       from interceptor_area
       where province is not null
        and city is null --区域异常,市为空
   ) t1 
   left join jms_dim.dim_network_whole_massage t2
       on t1.province = t2.provider_desc
   union all
   select 
        t2.code
       ,t2.name
       ,if(effect_time_start is null, '1999-01-01 01:01:01', effect_time_start) as effect_time_start
       ,if(effect_time_end   is null, '9999-01-01 01:01:01', effect_time_end)   as effect_time_end
   from (--区域异常,区域为空
        select *
        from interceptor_area
        where province is not null
          and city is not null
          and area is null
    ) t1 --区域异常,区域为空
    left join jms_dim.dim_network_whole_massage t2
       on t1.province = t2.provider_desc
      and city = t2.city_desc
   union all
   select t2.code
        , t2.name
        , t1.effect_time_start
        , t1.effect_time_end
   from (--区域异常,省市区都不为空
       select *
       from interceptor_area
       where province is not null
         and city is not null
         and area is not null
   ) t1 --区域异常,省市区都不为空
   left join jms_dim.dim_network_whole_massage t2
      on t1.province = t2.provider_desc
     and city = t2.city_desc
     and t1.area = t2.area_desc
),
interceptor_network as ( --网点异常 --存在维护的网点名称和网点表里的网点名称不一致
    select
         t1.network_code
        ,t2.name as network_name
        ,t1.effect_time_start
        ,t1.effect_time_end
    from (
        select
             t1.network_code
            ,if(t1.effect_time_start is null, '1999-01-01 01:01:01',t1.effect_time_start) as effect_time_start
            ,if(t1.effect_time_end   is null, '9999-01-01 01:01:01',t1.effect_time_end  ) as effect_time_end
        from jms_dim.dim_yl_oms_order_interceptor_base_dt t1
        where dt = date_sub('{{ execution_date | cst_ds }}',1)
          and t1.data_type = 'network'
          and t1.is_delete = 0  --未删除
          and t1.is_enable = 1  --已启用
          and t1.network_code is not null
          and ((t1.effect_time_start is not null and t1.effect_time_end is not null)
            or (t1.effect_time_start is null and t1.effect_time_end is null and t1.effect_time_type = 0))--生效时间类型 0 无限 ，1 有效时间断
        group by t1.network_code
               ,t1.effect_time_start
               ,t1.effect_time_end
     ) t1
     left join jms_dim.dim_network_whole_massage t2
        on t1.network_code = t2.code
)




insert overwrite table jms_dm.dm_waybill_collect_intime_interceptor_base
select 
     network_code
    ,network_name
    ,effect_time_start
    ,effect_time_end
from (
    select
         code              as network_code
        ,name              as network_name
        ,effect_time_start as effect_time_start
        ,effect_time_end   as effect_time_end
    from area_massage_all
    union all
    select
         network_code
        ,network_name
        ,effect_time_start as effect_time_start
        ,effect_time_end   as effect_time_end
    from interceptor_network
) a
where network_code is not null
group by network_code
        ,network_name
        ,effect_time_start
        ,effect_time_end
distribute by 1;
        